package com.x.dao.user;

import com.x.bean.Achievement;
import com.x.bean.User;
import com.x.dao.BaseDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 13925
 */
public class UserDaoImpl implements UserDao {

    @Override
    public User getLoginUser(Connection connection, String code, String password, int grade) throws SQLException {
        PreparedStatement pstmt = null;
        User user = null;
        ResultSet resultSet = null;

        if (connection != null) {
            String sql = "select * from user where code = ? and password = ? and grade = ?";
            Object[] params = {code, password, grade};
            resultSet = BaseDao.excuteQuery(connection, pstmt, resultSet, sql, params);

            if (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setCode(resultSet.getString("code"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setGrade(resultSet.getInt("grade"));
            }
            BaseDao.closeResource(null, pstmt, resultSet);
        }
        return user;
    }

    @Override
    public int addUser(Connection connection, User user) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "insert into user (id, code, name, password, grade) values(?, ?, ?, ?, ?)";
            Object[] params = {user.getId(), user.getCode(), user.getName(), user.getPassword(), user.getGrade()};
            updateRows = BaseDao.executeUpdate(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    @Override
    public int deleteUserByName(Connection connection, String code) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "delete from user where code = ?";
            Object[] params = {code};
            updateRows = BaseDao.executeUpdate(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    @Override
    public int updateUser(Connection connection, User user) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "update user set name = ?, password = ?, grade = ? where code = ?";
            Object[] params = {user.getName(), user.getPassword(), user.getGrade(), user.getCode()};
            updateRows = BaseDao.executeUpdate(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    @Override
    public List<User> getUserList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        List<User> userList = new ArrayList<User>();
        if (connection != null) {
            String sql = "select * from user";
            List<Object> list = new ArrayList<Object>();
            Object[] params = list.toArray();
            resultSet = BaseDao.excuteQuery(connection, pstm, resultSet, sql, params);

            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setCode(resultSet.getString("code"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setGrade(resultSet.getInt("grade"));
                userList.add(user);
            }
            BaseDao.closeResource(null, pstm, resultSet);
        }
        return userList;
    }

    @Override
    public User getUserByCode(Connection connection, String code) throws SQLException {
        PreparedStatement pstm = null;
        User user = null;
        ResultSet resultSet = null;

        if (connection != null) {
            String sql = "select * from user where code = ?";
            Object[] params = {code};
            resultSet = BaseDao.excuteQuery(connection, pstm, resultSet, sql, params);

            if (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setCode(resultSet.getString("code"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setGrade(resultSet.getInt("grade"));
            }
            BaseDao.closeResource(null, pstm, resultSet);
        }

        return user;
    }

    @Override
    public int addAchievement(Connection connection, Achievement achievement) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "insert into achievement (Aid, courseNo, courseName, score, code) values(?, ?, ?, ?, ?)";
            Object[] params = {achievement.getAid(), achievement.getCourseNo(), achievement.getCourseName(), achievement.getScore(), achievement.getCode()};
            updateRows = BaseDao.executeUpdate(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    @Override
    public int deleteAchievementByCodeAndCourseName(Connection connection, String code, String courseName) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "delete from achievement where code = ? and courseName = ?";
            Object[] params = {code, courseName};
            updateRows = BaseDao.executeUpdate(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    @Override
    public int updateAchievement(Connection connection, Achievement achievement) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "update achievement set score = ? where code = ? and courseName = ?";
            Object[] params = {achievement.getScore(), achievement.getCode(), achievement.getCourseName()};
            updateRows = BaseDao.executeUpdate(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    @Override
    public List<Achievement> getAchievementByCode(Connection connection, String code) throws SQLException {
        PreparedStatement pstm = null;
        ArrayList<Achievement> achievementsList = new ArrayList<>();
        ResultSet resultSet = null;

        if (connection != null) {
            String sql = "select courseNo, courseName, score, code from achievement where code = ?";
            List<Object> list = new ArrayList<Object>();
            list.add(code);
            Object[] params = list.toArray();
            resultSet = BaseDao.excuteQuery(connection, pstm, resultSet, sql, params);

            while (resultSet.next()) {
                Achievement achievement = new Achievement();
                achievement.setCourseNo(resultSet.getString("courseNo"));
                achievement.setCourseName(resultSet.getString("courseName"));
                achievement.setScore(resultSet.getString("score"));
                achievement.setCode(resultSet.getString("code"));
                achievementsList.add(achievement);
            }
            BaseDao.closeResource(null, pstm, resultSet);
        }
        return achievementsList;
    }

}

